from datetime import datetime, date, timedelta
from models import db, StatisticsData, Product, RepairRecord, MaintainRecord, InspectionRecord, Organization
from sqlalchemy import func, and_
from server import server

def calc_statistics_for_org(org):
    today = date.today()
    # 设备数量
    equipment_count = db.session.query(func.count(Product.id)).filter(
        Product.org_short_name_c == org.short_name_c,
        Product.is_active == 1
    ).scalar() or 0

    # 总价值
    total_value = db.session.query(func.sum(Product.price)).filter(
        Product.org_short_name_c == org.short_name_c,
        Product.is_active == 1
    ).scalar() or 0

    # 故障次数（所有时间）
    fault_count = db.session.query(func.count(RepairRecord.id)).filter(
        RepairRecord.org_shortname_c == org.short_name_c,
        RepairRecord.is_active == 1
    ).scalar() or 0

    # 维修完成次数（所有时间）
    repair_complete_count = db.session.query(func.count(RepairRecord.id)).filter(
        RepairRecord.org_shortname_c == org.short_name_c,
        RepairRecord.repair_status.in_(["已修复", "维修完成","已完成"]),
        RepairRecord.is_active == 1
    ).scalar() or 0

    # 维修完成率
    repair_complete_rate = round(repair_complete_count / fault_count, 4) if fault_count else 0

    # 设备完好率
    healthy_count = db.session.query(func.count(Product.id)).filter(
        Product.org_short_name_c == org.short_name_c,
        Product.is_active == 1,
        Product.running_status == 0
    ).scalar() or 0
    health_rate = round(healthy_count / equipment_count, 4) if equipment_count else 0

    # 年限指数
    age_index = 0
    products = db.session.query(Product).filter(
        Product.org_short_name_c == org.short_name_c,
        Product.is_active == 1,
        Product.purchase_date != None,
        Product.service_life > 0
    ).all()
    if products:
        total_index = 0
        today = date.today()
        for p in products:
            if p.purchase_date and p.service_life:
                years = (today - p.purchase_date).days / 365.25
                total_index += years / p.service_life
        age_index = round(total_index / len(products), 4)

    # 保养计划/完成次数（所有时间）
    maintain_plan_count = db.session.query(func.count(MaintainRecord.id)).filter(
        MaintainRecord.org_name == org.short_name_c,
        MaintainRecord.is_active == 1
    ).scalar() or 0
    maintain_complete_count = db.session.query(func.count(MaintainRecord.id)).filter(
        MaintainRecord.org_name == org.short_name_c,
        MaintainRecord.completion_status == "已完成",
        MaintainRecord.is_active == 1
    ).scalar() or 0
    maintain_complete_rate = round(maintain_complete_count / maintain_plan_count, 4) if maintain_plan_count else 0

    # 巡检计划/完成次数（所有时间）
    inspection_plan_count = db.session.query(func.count(InspectionRecord.id)).filter(
        InspectionRecord.org_name == org.short_name_c,
        InspectionRecord.is_active == 1
    ).scalar() or 0
    inspection_complete_count = db.session.query(func.count(InspectionRecord.id)).filter(
        InspectionRecord.org_name == org.short_name_c,
        InspectionRecord.completion_status == "已完成",
        InspectionRecord.is_active == 1
    ).scalar() or 0
    inspection_complete_rate = round(inspection_complete_count / inspection_plan_count, 4) if inspection_plan_count else 0

    return StatisticsData(
        org_id=org.id,
        org_name=org.org_name,
        org_shortname_c=org.short_name_c,
        stat_date=today,
        equipment_count=equipment_count,
        total_value=total_value,
        fault_count=fault_count,
        repair_complete_count=repair_complete_count,
        repair_complete_rate=repair_complete_rate,
        health_rate=health_rate,
        age_index=age_index,
        maintain_plan_count=maintain_plan_count,
        maintain_complete_count=maintain_complete_count,
        maintain_complete_rate=maintain_complete_rate,
        inspection_plan_count=inspection_plan_count,
        inspection_complete_count=inspection_complete_count,
        inspection_complete_rate=inspection_complete_rate,
        is_active=True,
        create_time=datetime.now(),
        update_time=datetime.now()
    )

def run_statistics_job():
    with server.app_context():
        orgs = db.session.query(Organization).filter(Organization.is_active == 1).all()
        for org in orgs:
            stat = calc_statistics_for_org(org)
            # 先删除当天该机构旧数据，避免重复
            db.session.query(StatisticsData).filter(
                StatisticsData.org_id == org.id,
                StatisticsData.stat_date == stat.stat_date
            ).delete()
            db.session.add(stat)
        db.session.commit()
        print(f"{datetime.now()} 统计数据已更新")

if __name__ == "__main__":
    print("p130 统计数据任务开始")
    run_statistics_job()